#!/usr/bin/env python 
# -*- coding: utf-8 -*- 
# @Time : 2018-05-14 13:46 
# @Author : Woolei
# @File : removeUselessCoverTool.py 


import sqlite3
import os
import logging

COVER_ROOT_PATH = 'E:\\down_data\\TandfBook\\tandfbook_cover_20180428'
DB3_PATH = 'E:\\down_data\\TandfBook\\zt_template_20180428.db3'

DB_CONFIG = {
    'host': '192.168.0.90',
    'user': 'root',
    'passwd': 'vipdatacenter',
    'db': 'TandfBook',
    'port': 3306
}

logging.basicConfig(level=logging.INFO,  # 最低输出
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S')


# 如果封面所带的isbn在数据库字段中不存在
# 移除无用的封面
def removeUselessCover(cover_path):
    pass
    # if os.path.exists(cover_path):
    #     os.remove(cover_path)
    # else:
    #     logging.warning('Cover path is not Exist: %s' % cover_path)


# 遍历封面图片
def traverseCoverJpg(path):
    for fpathe, dirs, fs in os.walk(path):
        for f in fs:
            cover_path = os.path.join(fpathe, f)
            if cover_path.endswith('.jpg'):  # 判断是否为jpg格式的图片
                cover_isbn = cover_path.split('\\')[-1].split('.')[0]
                yield cover_isbn, cover_path


# 查询是否存在该字段
def checkCover(conn, isbn):
    cursor = conn.cursor()
    Sql = '''SELECT COUNT(*) FROM modify_title_info_zt WHERE rawid="%s"''' % isbn
    cursor.execute(Sql)
    result = cursor.fetchall()
    if isinstance(result, list) and result[0][0]:
        return True
    else:
        return False


def start():
    db3_conn = sqlite3.connect(DB3_PATH)
    one_cover = traverseCoverJpg(COVER_ROOT_PATH)
    for each in one_cover:
        cover_isbn, cover_path = each
        status = checkCover(conn=db3_conn, isbn=cover_isbn)
        if not status:
            logging.warning('Cover Path removed: %s') % cover_path
            removeUselessCover(cover_path=cover_path)
        # logging.info('Current ISBN:%s, Cover count:%s, Update success:%s' % (cover_isbn, img_count, update_count))


if __name__ == '__main__':
    start()
